****************
ROWNUM Functions
****************

ROWNUM, INST_NUM
================

.. c:macro:: ROWNUM
.. function:: INST_NUM ()

    The **ROWNUM** function returns the number representing the order of the records that will be generated by the query result. The first result record is assigned 1, and the second result record is assigned 2.

    :rtype: INT

**ROWNUM** and **INST_NUM()** can be used in the **SELECT** statement, and **GROUPBY_NUM()** can be used in the **SELECT** statement with **GROUP BY** clauses. The **ROWNUM** function can be used to limit the number of result records of the query in several ways. For example, it can be used to search only the first 10 records or to return even or odd number records.

The **ROWNUM** function has a result value as an integer, and can be used wherever an expression is valid such as the **SELECT** or **WHERE** clause. However, it is not allowed to compare the result of the **ROWNUM** function with the attribute or the correlated subquery.

**Remark**

*   The **ROWNUM** function specified in the **WHERE** clause works the same as the **INST_NUM()** function. Whereas **INST_NUM()** is a scalar function, **GROUPBY_NUM()** is a kind of an aggregate function. In a **SELECT** statement with a **GROUP BY** clause, **GROUPBY_NUM()** must be used instead of **INST_NUM()**.

*   The **ROWNUM** function belongs to each **SELECT** statement. That is, if a **ROWNUM** function is used in a subquery, it returns the sequence of the subquery result while it is being executed. Internally, the result of the **ROWNUM** function is generated right before the searched record is written to the query result set. At this moment, the counter value that generates the serial number of the result set records increases.

*   If an **ORDER BY** clause is included in the **SELECT** statement, the value of the **ROWNUM** function specified in the **WHERE** clause is generated before sorting for the **ORDER BY**  clause. If a **GROUP BY** clause is included in the **SELECT** statement, the value of the **GROUPBY_NUM()** function specified in the **HAVING** clause is calculated after the query results are grouped. After the sorting process is completed using the **ORDER BY** clause, you need to use the **ORDERBY_NUM()** function in the **ORDER BY** clause in order to get a sequence of the result records.

*   The **ROWNUM** function can also be used in SQL statements such as **INSERT**, **DELETE** and **UPDATE** in addition to the **SELECT** statement. For example, as in the query **INSERT INTO** *table_name* **SELECT** ... **FROM** ... **WHERE** ..., you can search for part of the row from one table and then insert it into another by using the **ROWNUM** function in the **WHERE** clause.

The following example shows how to retrieve country names ranked first to fourth based on the number of gold (*gold*) medals in the 1988 Olympics in the *demodb* database.

.. code-block:: sql

    --Limiting 4 rows using ROWNUM in the WHERE condition
    SELECT  * FROM
    (SELECT nation_code FROM participant WHERE host_year = 1988
         ORDER BY gold DESC) AS T
    WHERE ROWNUM <5;
    
      nation_code
    ======================
      'URS'
      'GDR'
      'USA'
      'KOR'
     
    --Limiting 4 rows using FOR ORDERBY_NUM()
    SELECT ROWNUM, nation_code FROM participant WHERE host_year = 1988
    ORDER BY gold DESC
    FOR ORDERBY_NUM() < 5;
    
           rownum  nation_code
    ===================================
              156  'URS'
              155  'GDR'
              154  'USA'
              153  'KOR'
     
    --Unexpected results : ROWNUM operated before ORDER BY
    SELECT ROWNUM, nation_code FROM participant
    WHERE host_year = 1988 AND ROWNUM < 5
    ORDER BY gold DESC;
    
           rownum  nation_code
    ===================================
                1  'ZIM'
                2  'ZAM'
                3  'ZAI'
                4  'YMD'

GROUPBY_NUM
===========

.. function:: GROUPBY_NUM ()

    The **GROUPBY_NUM()** function is used with the **ROWNUM** or **INST_NUM()** function to limit the number of result rows. The difference is that the **GROUPBY_NUM()** function is combined after the **GROUP BY … HAVING** clause to give order to a result that has been already sorted. In addition, while the **INST_NUM()** function is a scalar function, the **GROUPBY_NUM()** function is kind of an aggregate function.

    :rtype: INT

That is, when retrieving only some of the result rows by using **ROWNUM** in a condition clause of the **SELECT** statement that includes the **GROUP BY** clause, **ROWNUM** is applied first and then group sorting by **GROUP BY** is performed. On the other hand, when retrieving only some of the result rows by using the **GROUPBY_NUM()** function, **ROWNUM** is applied to the result of group sorting by **GROUP BY**.

The following example shows how to retrieve the fastest record in the previous five Olympic Games from the *history* table in the  *demodb* database.

.. code-block:: sql

    --Group-ordering first and then limiting rows using GROUPBY_NUM()
    SELECT host_year, MIN(score) FROM history  
    GROUP BY host_year HAVING GROUPBY_NUM() BETWEEN 1 AND 5;
    
        host_year  min(score)
    ===================================
             1968  '8.9'
             1980  '01:53.0'
             1984  '13:06.0'
             1988  '01:58.0'
             1992  '02:07.0'
     
    --Limiting rows first and then Group-ordering using ROWNUM
    SELECT host_year, MIN(score) FROM history
    WHERE ROWNUM BETWEEN 1 AND 5 GROUP BY host_year;
    
        host_year  min(score)
    ===================================
             2000  '03:41.0'
             2004  '01:45.0'

ORDERBY_NUM
===========

.. function:: ORDERBY_NUM ()

    The **ORDERBY_NUM()** function is used with the **ROWNUM()** or **INST_NUM()** function to limit the number of result rows. The difference is that the **ORDERBY_NUM()** function is combined after the ORDER BY clause to give order to a result that has been already sorted. That is, when retrieving only some of the result rows by using **ROWNUM** in a condition clause of the **SELECT** statement that includes the **ORDER BY** clause, **ROWNUM** is applied first and then group sorting by **ORDER BY** is performed. On the other hand, when retrieving only some of the result rows by using the **ORDER_NUM()** function, **ROWNUM** is applied to the result of sorting by **ORDER BY**.

    :rtype: INT

The following example shows how to retrieve athlete names ranked 3rd to 5th and their records in the *history* table in the *demodb* database.

.. code-block:: sql

    --Ordering first and then limiting rows using FOR ORDERBY_NUM()
    SELECT athlete, score FROM history
    ORDER BY score FOR ORDERBY_NUM() BETWEEN 3 AND 5;
    
      athlete               score
    ============================================
      'Luo Xuejuan'         '01:07.0'
      'Rodal Vebjorn'       '01:43.0'
      'Thorpe Ian'          '01:45.0'
     
    --Limiting rows first and then Ordering using ROWNUM
    SELECT athlete, score FROM history
    WHERE ROWNUM BETWEEN 3 AND 5 ORDER BY score;
    
      athlete               score
    ============================================
      'Thorpe Ian'          '01:45.0'
      'Thorpe Ian'          '03:41.0'
      'Hackett Grant'       '14:43.0'